package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.dto.SysDepart;
import com.telecom.cms.dto.SysUser;
import com.telecom.cms.dto.SysUserEx;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class SysUserDAO {

	Dao dao = new DaoImpStd();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(SysUser transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db + ".Sys_User where User_id="
				+ transientInstance.getUserId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next()) {
				sql = "update " + db + ".Sys_User set latn_id='"

				        + transientInstance.getLatnId() + "',user_name='"
						+ transientInstance.getUserName() + "',user_pass='"
						+ Constant.EasyCode(transientInstance.getUserPass()) + "',depart_id ='"
						+ transientInstance.getDepartId() + "',team_id='"
						+ transientInstance.getTeamId() + "',user_role='"
						+ transientInstance.getUserRole() + "',user_status='"
						+ transientInstance.getUserStatus()
						+ "',last_login_time= timestamp '"
						+ CurrentTime.getCurrentTime()
						+ "',Creator_Name='"
						+ transientInstance.getCreatorName() + "',nick_name='" 
						+ transientInstance.getNickName()+"' where User_id="
						+ transientInstance.getUserId();

			}

			else {
				sql = "insert into " + db + ".Sys_User values(,'"
						+ transientInstance.getLatnId() + "','"
						+ transientInstance.getUserName() + "','"
						+ Constant.EasyCode(transientInstance.getUserPass()) + "','"
						+ transientInstance.getDepartId() + "','"
						+ transientInstance.getTeamId() + "','"
						+ transientInstance.getUserRole() + "','"
						+ transientInstance.getUserStatus() + "',timestamp '"
						+ CurrentTime.getCurrentTime() + "','"
						+ transientInstance.getCreatorName() + "',timestamp '"
						+ CurrentTime.getCurrentTime()+ "','"
						+ transientInstance.getNickName()+ "')";

			}
			rs = dao.insert(sql);
			if (rs2 != null) {

				rs2.close();
			}
			

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(SysUser persistentInstance) {

		String sql = "delete from " + db + ".Sys_User where User_Id = "
				+ persistentInstance.getUserId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public SysUser findById(int id) {

		String sql = "select * from " + db + ".Sys_User where User_Id = " + id;
		SysUser sd = new SysUser();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setUserId(id);
				sd.setLatnId(rs.getString(2));
				sd.setUserName(rs.getString(3));
				sd.setUserPass(rs.getString(4));
				sd.setDepartId(rs.getInt(5));
				sd.setTeamId(rs.getInt(6));
				sd.setUserRole(rs.getString(7));
				sd.setUserStatus(rs.getString(8));
				sd.setLastLoginTime(rs.getString(9));
				sd.setCreatorName(rs.getString(10));
				sd.setCreateTime(rs.getString(11));
				sd.setNickName(rs.getString(12));
			}
			if (rs != null) {

				rs.close();
			}
			

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return sd;

	}
	public SysUser findById(String username,String passwd) {

		String sql = "select * from " + db + ".Sys_User where User_Name = '" + username+"' and user_pass='"+Constant.EasyCode(passwd)+"' ";
		if(Constant.getStPara("ISPASSENCODE").trim().equals("1"))
			sql +=" union select * from " + db + ".Sys_User where User_Name = '" + username+"' and user_pass='"+passwd+"' ";
		SysUser sd = null;
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd=new SysUser();
				sd.setUserId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setUserName(rs.getString(3));
				sd.setUserPass(rs.getString(4));
				sd.setDepartId(rs.getInt(5));
				sd.setTeamId(rs.getInt(6));
				sd.setUserRole(rs.getString(7));
				sd.setUserStatus(rs.getString(8));
				sd.setLastLoginTime(rs.getString(9));
				sd.setCreatorName(rs.getString(10));
				sd.setCreateTime(rs.getString(11));
				sd.setNickName(rs.getString(12));
			}
			if (rs != null) {

				rs.close();
			}
			

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select a.*,b.latn_name,c.depart_name,d.team_name from " + db + ".Sys_User a "+
		"left join " + db + ".sys_loc_latn b "+
		"on a.latn_id=b.latn_id "+
		"left join " + db + ".Sys_Depart c "+
		"on a.depart_id=c.depart_id "+
		"left join "+db+".Sys_Team d "+
		"on a.team_id=d.team_id " +
		"order by a.user_name";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysUserEx sd = new SysUserEx();

				sd.setUserId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setUserName(rs.getString(3));
				sd.setUserPass(rs.getString(4));
				sd.setDepartId(rs.getInt(5));
				sd.setTeamId(rs.getInt(6));
				sd.setUserRole(rs.getString(7));
				sd.setUserStatus(rs.getString(8));
				sd.setLastLoginTime(rs.getString(9));
				sd.setCreatorName(rs.getString(10));
				sd.setCreateTime(rs.getString(11));
				sd.setNickName(rs.getString(12));
				sd.setLatnName(rs.getString(13));
				sd.setDepartName(rs.getString(14));
				sd.setTeamName(rs.getString(15));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}
			

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAll(String latnid) {

		String sql = "select a.*,b.latn_name,c.depart_name,d.team_name from " + db + ".Sys_User a "+
		"left join " + db + ".sys_loc_latn b "+
		"on a.latn_id=b.latn_id "+
		"left join " + db + ".Sys_Depart c "+
		"on a.depart_id=c.depart_id "+
		"left join "+db+".Sys_Team d "+
		"on a.team_id=d.team_id where a.latn_id='"+latnid+"'";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysUserEx sd = new SysUserEx();
				sd.setUserId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setUserName(rs.getString(3));
				sd.setUserPass(rs.getString(4));
				sd.setDepartId(rs.getInt(5));
				sd.setTeamId(rs.getInt(6));
				sd.setUserRole(rs.getString(7));
				sd.setUserStatus(rs.getString(8));
				sd.setLastLoginTime(rs.getString(9));
				sd.setCreatorName(rs.getString(10));
				sd.setCreateTime(rs.getString(11));
				sd.setNickName(rs.getString(12));
				sd.setLatnName(rs.getString(13));
				sd.setDepartName(rs.getString(14));
				sd.setTeamName(rs.getString(15));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}
			

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public List findAllOuter(String latnid) {

		String sql = "select a.*,b.latn_name,c.depart_name,d.team_name from " + db + ".Sys_User a "+
		"left join " + db + ".sys_loc_latn b "+
		"on a.latn_id=b.latn_id "+
		"left join " + db + ".Sys_Depart c "+
		"on a.depart_id=c.depart_id "+
		"left join "+db+".Sys_Team d "+
		"on a.team_id=d.team_id where a.latn_id='"+latnid+"' and a.user_role='"
		+ Constant.getStPara("CallerRoleId")+"' ";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				SysUserEx sd = new SysUserEx();
				sd.setUserId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setUserName(rs.getString(3));
				sd.setUserPass(rs.getString(4));
				sd.setDepartId(rs.getInt(5));
				sd.setTeamId(rs.getInt(6));
				sd.setUserRole(rs.getString(7));
				sd.setUserStatus(rs.getString(8));
				sd.setLastLoginTime(rs.getString(9));
				sd.setCreatorName(rs.getString(10));
				sd.setCreateTime(rs.getString(11));
				sd.setNickName(rs.getString(12));
				sd.setLatnName(rs.getString(13));
				sd.setDepartName(rs.getString(14));
				sd.setTeamName(rs.getString(15));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}
			

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally
		{
			dao.closeconn();
		}
		return list;

	}

}